package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.CourseInfo;
import com.eece496.Helpers.StudentInfo;
import com.eece496.Helpers.TAInfo;

public class TAEnrollment extends Database {

	//private int enrollmentID;
		private int TAID;
		private String courseNumber;
		private String sectionNumber;
		
		public TAEnrollment()
		{
			tableName = "TAEnrollment";	
		}
		
		public TAEnrollment(int TAID, String courseNumber, String sectionNumber)
		{
			tableName = "TAEnrollment";
			
			this.TAID = TAID;
			this.courseNumber = courseNumber;
			this.sectionNumber = sectionNumber;
			
		}
		
		@Override
		public void Insert() throws SQLException {
			Statement stmt = connection.createStatement();
			
			String sqlString = "INSERT INTO " + tableName + " VALUES (" +
					TAID + ", " +			
					AddQuotes(courseNumber)  + ", " +
					AddQuotes(sectionNumber) +
					")";
			
		    stmt.executeUpdate(sqlString);
		    System.out.println(sqlString);
		}

		@Override
		public void Delete(String primaryKey) throws SQLException {
			Statement stmt = connection.createStatement();
			
			String sqlString = "DELETE from " + tableName + " WHERE studentID = " + TAID
								+ " and courseNumber = " + AddQuotes(courseNumber);
			stmt.executeUpdate(sqlString);

		}

		//dont delete- created by Steven Chow
		public void DeleteStudentEnrollment(int TAID, String courseNumber, String sectionNumber) throws SQLException
		{
			Statement stmt = connection.createStatement();

			String sqlString = "DELETE from " + tableName + " WHERE TAID = " + TAID + " and courseNumber = "
					+ AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
			stmt.executeUpdate(sqlString);
		}
		@Override
		public void Display() throws SQLException {
			// TODO Auto-generated method stub

		}

		//Don't delete- Created by Steven Chow
		public ArrayList<CourseInfo> GetTACourseInfo(int TAID)
				throws SQLException {
			ArrayList<CourseInfo> list = new ArrayList<CourseInfo>();
			
			Statement stmt = connection.createStatement();
			
			String sqlString = "select course.courseNumber, course.courseName, "
					+ "course.sectionNumber, course.termYear, course.term, course.instructor "
					+ "from course inner join "
					+ tableName
					+ " on course.courseNumber = TAenrollment.courseNumber "
					+ " and course.sectionNumber = TAenrollment.sectionNumber where TAenrollment.TAID = "
					+ TAID;
			System.out.println(sqlString);
			ResultSet rs = stmt.executeQuery(sqlString);

			while (rs.next())
			{
				CourseInfo info = new CourseInfo();

				info.courseNumber = rs.getString("courseNumber");
				info.courseName = rs.getString("courseName");
				info.sectionNumber = rs.getString("sectionNumber");
				info.termYear = rs.getInt("termYear");
				info.term = rs.getString("term");
				info.instructor = rs.getString("instructor");
				list.add(info);
			}

			return list;
		}
		
		public ArrayList<TAInfo> GetAllTAsEnrolled(String courseNumber, String sectionNumber) throws SQLException
		{
			
			ArrayList<TAInfo> list = new ArrayList<TAInfo>();
			
			Statement stmt = connection.createStatement();
			String sqlString = "select teachingassistant.TAID, teachingassistant.firstname, teachingassistant.lastname " +
					"from teachingassistant inner join taenrollment "
					+ "on teachingassistant.TAID = taenrollment.TAID where courseNumber = " + AddQuotes(courseNumber) + " and "
					+ " sectionNumber = " + AddQuotes(sectionNumber);
			
			ResultSet rs = stmt.executeQuery(sqlString);
			
			while(rs.next())
			{
				TAInfo info = new TAInfo();
				
				info.TAID = rs.getInt("TAID");
				info.firstname = rs.getString("firstName");
				info.lastname = rs.getString("lastName");
				
				list.add(info);
			}
			return list;
		}
		
		public void DeleteCourseEnrollment(String courseNumber, String sectionNumber) throws SQLException
		{
			Statement stmt = connection.createStatement();
			
			String sqlString = "DELETE from " + tableName + " WHERE courseNumber = " + AddQuotes(courseNumber)
								+ " and sectionNumber = " + AddQuotes(sectionNumber);
			stmt.executeUpdate(sqlString);
		}
}
